let express = require("express")();
let expressStatic = require("express-static");
let mysql = require("mysql");
let bodyParser = require("body-parser");
let port = 81;

//为body-parser进行配置
express.use(bodyParser.json());
express.use(bodyParser.urlencoded({ extended: false }));

//规划数据库
let sql = mysql.createConnection({
    host: 'localhost',
    password: '123456',
    user: 'root',
    database: 'sims',
    timezone: '08:00'
})

//连库
sql.connect();

//接收前端请求查库
express.get("/getStudentList", (request, response) => {
    sql.query(`SELECT * FROM stuinfo`, (error, data) => {
        if (error) {
            response.send("error");
            console.log(error)
        } else {
            response.send(data);
        }
    })
})

//接收前端删除学生的请求
express.get("/deleteStudent", (request, response) => {
    sql.query(`DELETE FROM stuinfo WHERE id=${request.query.id}`, (error, data) => {
        if (error) {
            response.send("error");
            console.log(error)
        } else {
            response.send("success");
        }
    })
})

//接收前端添加学生的请求
express.get("/addStudent", (request, response) => {
    let s = request.query;
    sql.query(`INSERT INTO stuinfo (name,age,sex,city,joindate) VALUES ("${s.name}","${s.age}","${s.sex}","${s.city}","${s.joinDate}") `, (error, data) => {
        if (error) {
            response.send("error");
            console.log(error)
        } else {
            response.send("success");
            console.log(`添加学生${s.name}操作成功`)
        }
    })
})

//编辑请求
express.get("/newUser", (request, response) => {
    let n = request.query;
    sql.query(`UPDATE stuinfo SET name = "${n.name}",age = "${n.age}",sex = "${n.sex}",city = "${n.city}",joindate = "${n.joindate}" WHERE id="${n.id}"`, (error, data) => {
        if (error) {
            response.send("error");
            console.log(error)
        } else {
            response.send("success");
            console.log(`编辑学生${n.name}操作成功`)
        }
    })
})


//接收前端用户登录请求
express.post("/login", (request, response) => {
    let u = request.body;
    sql.query(`SELECT * FROM users WHERE username="${u.username}" AND password="${u.password}" `, (error, data) => {
        if (error) {
            response.send("3");
            console.log(error)
        } else {
            if (data.length === 0) {
                response.send("2");
            } else {
                response.send(data);
            }
        }
    })
})

//接收前端用户注册请求
express.post("/register", (request, response) => {
    let r = request.body;
    sql.query(`INSERT INTO users (username,password,name,usertype) VALUES("${r.username}","${r.password}","${r.reallyName}","${r.usertype}") `, (error, data) => {
        if (error) {
            response.send("error");
            console.log(error)
        } else {
            response.send("success");
            console.log(`${r.reallyName}用户注册成功`)
        }
    })
})


express.use(expressStatic(__dirname + "/static"));
express.listen(port);
console.log(`running at ${port}`)